OpenEdge Development:<br />Internationalizing Applications Using collation tables

Each database has its own collation tables OpenEdge uses for index operations. The 4GL also uses collation tables for string comparisons. The collation tables specify the order in which characters sort. These tables are named CASE-INSENSITIVE-SORT and CASE-SENSITIVE-SORT. The CASE-INSENSITIVE-SORT table sorts uppercase and lowercase letters identically. The CASE-SENSITIVE-SORT table distinguishes between uppercase and lowercase letters. To control how OpenEdge sorts characters, you can modify these tables. Each collation table consists of 256 cells (16 rows of 16 cells), each of which contains a decimal numeric value.

For each character in a string, OpenEdge indexes the character’s numeric value from the collation table to a cell in one of these tables. For example, if a character has a numeric value of 233, OpenEdge goes to cell 233 in the table. After OpenEdge locates the appropriate cell, it reads the value in the cell to find the character’s sort weight. The sort weight tells OpenEdge where you want the character sorted relative to other characters in the code page. For example, a sort weight of 001 means that the character sorts first in an ascending sort.

Collation tables must exist for each code page. You can have more than one collation per code page. To find the correct collation table, OpenEdge uses the values of the -cpinternal and -cpcoll startup parameters as keys to the convmap.cp file. The same collation name can appear for multiple code pages.

The following rules determine how OpenEdge decides which collation table to use when performing comparisons or sorting:

If you specify collation tables with the Collation Table (-cpcoll) startup parameter, OpenEdge uses those collation tables.

If you name databases in the startup or connection command and do not use -cpcoll, OpenEdge uses the collation table defined for the first database. If you connect to an additional database, OpenEdge uses the collation table of the additional database when working with the additional database.

If you do not name databases in the startup or connection command and do not use -cpcoll, OpenEdge searches convmap.cp to find the collation table named BASIC for the internal code page.

Note: The collation table you specify with the -cpcoll startup parameter must be in the DLC/convmap.cp file, which is a binary file that contains tables for managing characters created by compiling the DLC/prolang/convmap/convmap.dat file. If convmap.dat does not have a collation table you need, you can create your own.

4GL Comparisons

OpenEdge provides two types of character sorting. One type governs how data is stored within a database and affects index operations. Another type affects 4GL comparisons—comparisons that are performed by the 4GL and that do not impact indexes. For example, this is a typical 4GL comparison:

The character expressions can contain character strings, character variables, or character fields.

Database collation

IF `character-expression1` > `character-expression2`

A database has a collation table stored as part of its schema information. The collation table that is stored in the database does not affect how the OpenEdge client or server compares or sorts CHARACTER data (except for indexed data); it controls only how indexes are sorted and assigned. Similarly, a collation table specified by -cpcoll does not affect the internal collation order that a database uses for its indexes.

OpenEdge supplies collation tables for databases for many languages and locales. These are located in the DLC/prolang subdirectories. For languages that have one commonly used collation table, the table is contained in the _tran.df file in the language subdirectory. If more than one collation table is common, each is contained in a data definition (.df) file named for the table’s corresponding code page. To change the collation table associated with a database, load the appropriate data definition file and rebuild your indexes.